
// PREPAYMENT OF UNDERWATER MORTGAGES: ANALYSIS DOFILE


// I. CALL IN DATA FROM "data dofile"

clear all

// Project directory

cd "D:\Box\Box\Moussa\Research\Underwater Mortgages\stata_sas_codes\JFQA Codes and Data"  // project directory


// GETTING FHFA TRACT AND ZIP LEVEL HPI DATA

import delimited ".\input_data\HPI_AT_BDL_tract.csv", stringcols(1) clear

drop hpi1990 hpi2000

sort tract year

by tract: gen lead_hpi = hpi[_n+1]

save ".\output_data\HPI_AT_BDL_tract.dta", replace


import excel ".\input_data\HPI_AT_BDL_ZIP5.xlsx", sheet("ZIP5") cellrange(A7:F599) firstrow case(lower) clear // Change F599 to match the real data set; if was F630585 for our original data

destring year annualchange hpi hpiwith1990base hpiwith2000base, replace

drop hpiwith1990base hpiwith2000base

tostring fivedigitzipcode, replace

sort fivedigitzipcode year

by fivedigitzipcode: gen led_hpi = hpi[_n+1]

save ".\output_data\HPI_AT_BDL_ZIP5.dta", replace


// CALLING STUDY SAMPLE (created with data_dofile)

clear all

use ".\output_data\study_sample_final.dta", clear  // Calling in study sample


// 1. CREATING ESTIMATION VARIABLES

// Loan Purpose

tab loanpurposefk, m

gen loanpurp_refi=1 if !inlist(loanpurposefk,"P","")
replace loanpurp_refi=0 if loanpurp_refi==.
label var loanpurp_refi "Refinancing Loan"

gen loanpurp_miss=1 if loanpurposefk==""
replace loanpurp_miss=1 if loanpurp_miss==.
label var loanpurp_miss "Loan Purpose Missing"

// Occupancy

tab occupancystatusfk, m

gen nonowneroccup=0
replace nonowneroccup=1 if occupancystatusfk=="NO" | occupancystatusfk=="SH"
label var nonowneroccup "Non-Owner Occupancy"

gen occupunknown=0
replace occupunknown=1 if inlist(occupancystatusfk,"NA","O","")
label var occupunknown "Occupancy Unknown"

// Interest Rate

sum originalinterestrate, d

winsor originalinterestrate, gen(interest_win) p(0.01)
label var interest_win "Interest Rate"

// Loan Term

tab originalamortizationterm, m

gen log_term=log(originalamortizationterm)
label var log_term "Loan Term"

gen term_miss=1 if originalamortizationterm==.
replace term_miss=0 if term_miss==.
label var term_miss "Loan Term Missing"

// CLTV and LTV

sum originalcltv, d

label var originalcltv "CLTV"

sum originalltv, d

label var originalltv "LTV"

// Second Mortgages

sum originalltv originalcltv

count if originalcltv > originalltv & originalcltv != .

count if absnetloanfk2 != .

count if originalcltv > originalltv & originalcltv != . & absnetloanfk2 != .


// COMPUTING REMAINING BALANCES ON MATCHED SECOND MORTGAGES

count if actualendingbalance2 == 0 & absnetloanfk2 != .

count if endingpoolbalance2 == 0 & absnetloanfk2 != .

count if actualendingbalance2 == 0 & endingpoolbalance2 == 0 & absnetloanfk2 != .

gen endbal_2ndmortg = 0 if actualendingbalance2 == 0 & endingpoolbalance2 == 0 & absnetloanfk2 != . // setting 2nd mortgage bal equal to zero only if actualendingbalance2=0 and endingpoolbalance2=0

replace endbal_2ndmortg = actualendingbalance2 if endbal_2ndmortg == . & !inlist(actualendingbalance2,0,.) & absnetloanfk2 != .

replace endbal_2ndmortg = endingpoolbalance2 if endbal_2ndmortg == . & !inlist(endingpoolbalance2,0,.) & absnetloanfk2 != .

assert endbal_2ndmortg != . if absnetloanfk2 != .

save ".\output_data\study_sample_final_temp1.dta", replace


// Keeping second mortgage data

keep if absnetloanfk2 != .

assert endbal_2ndmortg != .

gen endbal_2ndmortg_ratio = endbal_2ndmortg/originalloanbalance2

sum endbal_2ndmortg_ratio

br if endbal_2ndmortg_ratio > 1

tab origyear2 repyear2

collapse (mean) mean_endbal_2ndmortg_ratio = endbal_2ndmortg_ratio (count) count_endbal_2ndmortg_ratio = endbal_2ndmortg_ratio , by (origyear repyear2)

rename repyear2 year

save ".\output_data\second_mortgages.dta", replace


// Merging Second Mortgage Termination Data to main dataset

use ".\output_data\study_sample_final_temp1.dta", clear

merge m:1 origyear year using ".\output_data\second_mortgages.dta"

drop _merge


// 2. EQUITY AND NEGATIVE EQUITY VARIABLES USING 1ST AND MATCHED 2ND MORTGAGES

br originalcltv originalltv if originalcltv == . | originalltv == .

replace originalcltv = originalltv if originalcltv == .

replace originalltv = originalcltv if originalltv == .

count if originalcltv < originalltv

replace originalcltv = originalltv if originalcltv < originalltv // replace CLTV with LTV if CLTV < LTV

count if endbal_2ndmortg != .

gen origbal_2ndmortg_estim = originalloanbalance/originalltv*(originalcltv - originalltv) if endbal_2ndmortg == . // Compute original 2nd mortgage balance if 2nd mortgage not found

order origbal_2ndmortg_estim mean_endbal_2ndmortg_ratio count_endbal_2ndmortg_ratio, before(endbal_2ndmortg)

replace origbal_2ndmortg_estim = 0 if origbal_2ndmortg_estim < 5000 & endbal_2ndmortg == .  // Removing estimated seconds of less than $5,000

replace endbal_2ndmortg = origbal_2ndmortg_estim * mean_endbal_2ndmortg_ratio if endbal_2ndmortg == . // computing ending balance on second mortgages

// assert endbal_2ndmortg != .    // May include this test when running on real data

drop equity_lien12 endloanbal_1st2ndlien negequity_lien12  // drop previous 1st-2nd lien variables

rename endbal_2ndmortg endbal_2ndmortg_match

gen endloanbal_1st2nd_match = endloanbal + endbal_2ndmortg_match

gen equity_1st2nd_match = adj_propvalue - endloanbal_1st2nd_match

sum equity_1st2nd_match
 
gen equity_1st2nd_ratio_match = equity_1st2nd_match / adj_propvalue

label var equity_1st2nd_ratio_match "Equity Ratio 1st2nd Match"

sum equity_1st2nd_ratio_match, d

gen negequity_1st2nd_match_dum = (equity_1st2nd_ratio_match < 0)

label var negequity_1st2nd_match "Negative Equity 1st2nd Dummy Match"

// Deleting Loans with Derived LTV Outside of [25, 125]

gen derived_ltv = originalloanbalance / originalappraisedvalue

sum derived_ltv

count if derived_ltv < .25 | derived_ltv > 1.25

drop if derived_ltv < .25 | derived_ltv > 1.25


// 3. EQUITY AND NEGATIVE EQUITY VARIABLES 1ST AND 2ND LIENS USING LTV AND CLTV

drop origloanbal_clv endloanbal_cltv equity_cltv negequity_cltv 

gen origbal_2ndmortg_cltv = originalloanbalance/originalltv*(originalcltv - originalltv)

replace origbal_2ndmortg_cltv = 0 if origbal_2ndmortg_cltv < 5000 // Removing estimated seconds of less than $5,000

gen endbal_2ndmortg_cltv = endloanbal / originalloanbalance * origbal_2ndmortg_cltv // computing ending balance on second mortgages based on balance on 1st mortgages

gen endloanbal_1st2nd_cltv = endloanbal + endbal_2ndmortg_cltv

gen equity_1st2nd_cltv = adj_propvalue - endloanbal_1st2nd_cltv

sum equity_1st2nd_cltv
 
gen equity_1st2nd_ratio_cltv = equity_1st2nd_cltv / adj_propvalue

label var equity_1st2nd_ratio_cltv "Equity Ratio 1st2nd CLTV"

sum equity_1st2nd_ratio_cltv, d

gen negequity_1st2nd_cltv_dum = (equity_1st2nd_ratio_cltv < 0)

label var negequity_1st2nd_cltv "Negative Equity 1st2nd Dummy CLTV"


// 4. FORMATTING OTHER CONTROL VARIABLES

// DTI

sum debttoincomeratio, d

gen dti_miss=1 if debttoincomeratio<10 | debttoincomeratio==.
replace dti_miss=0 if dti_miss==.
label var dti_miss "DTI Missing"

gen dti=debttoincomeratio if dti_miss==1
replace dti=0 if dti==.
winsor dti, gen(dti_win) p(0.0025) highonly

replace dti=dti_win
drop dti_win

label var dti "DTI"

// PMI

tab pmiflag, m

gen pmi=0
replace pmi=1 if pmiflag=="True"
label var pmi "PMI"

gen pmi_miss=0
replace pmi_miss=1 if pmiflag==""
label var pmi_miss "PMI Missing"

// NEG AMORT

tab negativeamortizationflag, m

gen negamort=0
replace negamort=1 if negativeamortizationflag=="True"
label var negamort "Neg. Amortization"

gen negamort_miss=0
replace negamort_miss=1 if negativeamortizationflag==""
label var negamort_miss "Neg. Amortization Missing"

// ARM

tab armflag, m

gen arm=0
replace arm=1 if armflag=="True"
label var arm "ARM"

// BALLOON

tab balloonflag, m

gen balloon=0
replace balloon=1 if balloonflag=="True"
label var balloon "Balloon"

// IO

tab interestonlyflag, m

gen io=0
replace io=1 if interestonlyflag=="True"
label var io "Interest Only"

gen io_miss=0
replace io_miss=1 if interestonlyflag==""
label var io_miss "Interest Only Missing"

// CREDIT SCORE

sum creditscore, d

label var creditscore "Credit Score"

gen creditscore_sh=creditscore/100
label var creditscore_sh "Credit Score (00s)"

gen log_cscore=log(creditscore)
label var log_cscore "Credit Score (log)"

// UNITS

tab numberofunits, m

gen singlefam=0
replace singlefam=1 if numberofunits==1
label var singlefam "Single Family"

// LOAN AMOUNT, APPRAISAL

sum originalloanbalance, d

gen log_loanamount=log(originalloanbalance)
label var log_loanamount "Loan Amount"

sum originalappraisedvalue, d

gen log_apprais=log(originalappraisedvalue)
label var log_apprais "Appraisal Value"

label var cpi "Inflation"
label var mortgage30yr "Mortgage Rates"
label var unemployment "Unemployment Rate"
label var hpi_end "HPI End"
label var hpi_orig "HPI Origination"
label var hpi_sd "HPI Volatility"


// 5. IDENTIFYING PREPAYMENT, REFINANCING, AND FORECLOSURE VARIABLES AFTER SECOND ABSNET-REALTY TRAC MATCHING.

tab purchase, m

tab purchase_2, m

tab purchase_2 if enddate_diff_asshist != ., m

replace purchase_2 = 1 if enddate_diff_asshist != . & purchase_2 == . // THESE ARE TRANSACTIONS IDENTIFIED FROM THE ASSESSOR HISTORY DATA

tab purchase_2 if sr_property_id !=. & paidoff == 1, m

// Prepayment - Loans repaid from sale of property

gen prepayment=1 if paidoff==1 & purchase_2==1
replace prepayment = 0 if prepayment==. & sr_property_id!=.

replace prepayment = . if prepayment == 0 & sr_property_id !=. & paidoff == 1 & purchase_2 == .

label var prepayment "Sold"

tab prepayment, m 

tab prepayment if sr_property_id !=. & paidoff == 1, m 

// Refinancing - Loans repaid from refinancing

gen refinancing=1 if paidoff==1 & purchase_2==0
replace refinancing=0 if refinancing==. & sr_property_id!=. 

replace refinancing = . if refinancing == 0 & sr_property_id !=. & paidoff == 1 & purchase_2 == .

label var refinancing "Refinanced"

tab refinancing, m

tab refinancing if sr_property_id !=. & paidoff == 1, m 

// Foreclosure - Loans foreclosed

gen foreclosure=1 if foreclosed==1 & sr_property_id!=.
replace foreclosure=0 if sr_property_id!=. & foreclosure==. 

replace foreclosure = . if foreclosure == 0 & sr_property_id !=. & paidoff == 1 & purchase_2 == .

label var foreclosure "Foreclosed"

tab foreclosure, m

tab foreclosure if sr_property_id !=. & paidoff == 1, m 

tab prepayment if foreclosure !=., m

tab refinancing if foreclosure !=., m

tab active if foreclosure != .

assert prepayment + refinancing + foreclosure + active == 1 if foreclosure != .


// 6. CREDIT SCORE QUINTILES BASED ON MATCHED SAMPLE

gen loan_outcome1="Paidoff" if paidoff==1

replace loan_outcome1="Foreclosed" if foreclosed==1

replace loan_outcome1="Active" if active==1

xtile crdscore_quint_matched = creditscore if sr_property_id!=., nq(5)

tab crdscore_quint_matched

bys crdscore_quint_matched: sum creditscore

gen crdscore_quint1_matched= crdscore_quint_matched==1 if sr_property_id != .
tab crdscore_quint1_matched

gen crdscore_quint2_matched= crdscore_quint_matched==2 if sr_property_id != .
gen crdscore_quint3_matched= crdscore_quint_matched==3 if sr_property_id != .
gen crdscore_quint4_matched= crdscore_quint_matched==4 if sr_property_id != .
gen crdscore_quint5_matched= crdscore_quint_matched==5 if sr_property_id != .

assert crdscore_quint1_matched + crdscore_quint2_matched + crdscore_quint3_matched + crdscore_quint4_matched + crdscore_quint5_matched if sr_property_id!=.

label var crdscore_quint1_matched "Credit Score Quintile 1 Matched"
label var crdscore_quint1_matched "Credit Score Quintile 2 Matched"
label var crdscore_quint1_matched "Credit Score Quintile 3 Matched"
label var crdscore_quint1_matched "Credit Score Quintile 4 Matched"
label var crdscore_quint1_matched "Credit Score Quintile 5 Matched"


gen medianinc=median_inc_owner_200716/1000

label var medianinc "Median Income (000s)"

gen log_adjvalue=ln(adj_propvalue)

label var log_adjvalue "Terminal Property Value"

label variable prepayment "Repaid"
 

gen loanpayment = 12*originalloanbalance*(originalinterestrate/100/12)*(1+originalinterestrate/100/12)^(originalamortizationterm*12)/((1+originalinterestrate/100/12)^(originalamortizationterm*12)-1)

label var loanpayment "Annual Mortgage Payments"

gen income_estim = loanpayment/debttoincomeratio*100/1000 

label var income_estim "Income Estimate (000s)"

sum income_estim if sr_property_id!=. & (prepayment==1 | foreclosed==1) & shortsaleflag=="False"


gen default=1 if prepayment==1 & inlist(delinquentdaysmba,"30","60","90","120","150+")

replace default=0 if default==.


// II. NEW ESTIMATIONS USING TRACT AND FIVE-DIGIT ZIP HPIs 

// 1. ADDING TRACT FROM REALTYTRAC
 
drop sa_property_id

merge m:1 sr_property_id using ".\input_data\properties_geos.dta", keepusing(sr_property_id sa_property_id sa_x_coord_md sa_y_coord_md sa_geo_qlty_code_md sa_census_tract_md sa_census_block_group_md core_based_statistical_area_cod minor_civil_division_code_md fips_place_code_md)

drop if _merge == 2
drop _merge

count if sr_property_id != . & sa_census_tract_md != ""

br sa_census_tract_md statecode mm_fips_county_name mm_fips_muni_code mm_fips_state_code

rename sa_census_tract_md tract

gen state = mm_fips_state_code
tostring state, replace

replace state = "0" + state if strlen(state) == 1

replace state = "" if mm_fips_state_code == .

gen county = mm_fips_muni_code
tostring county, replace

replace county = "" if mm_fips_muni_code == .

replace county = "00" + county if strlen(county) == 1

replace county = "0" + county if strlen(county) == 2

assert strlen(county) <= 3

gen tract_code = state + county + tract 

replace tract_code = "" if strlen(tract) != 6


// 2. ADDING TRACT HPI DATA AT ORIGINATION - USE ZIP HPI IF TRACT HPI MISSING

rename (year month)(repyear repmonth)

rename origyear year

rename tract _tract

rename tract_code tract

merge m:1 tract year using ".\output_data\HPI_AT_BDL_tract.dta", keepusing(hpi lead_hpi)

drop if _merge == 2

drop _merge

br year lead_hpi hpi tract county state absnetloanfk sr_property_id if sr_property_id != .

count if sr_property_id != . & hpi != .

rename (hpi lead_hpi)(hpi_tract lead_hpi_tract)


rename zipcode fivedigitzipcode

merge m:1 fivedigitzipcode year using ".\output_data\HPI_AT_BDL_ZIP5.dta", keepusing(hpi led_hpi)

drop if _merge == 2

drop _merge

rename (hpi led_hpi)(hpi_zip lead_hpi_zip)


replace hpi_tract = . if hpi_tract !=. & lead_hpi_tract == . // deleting obs with missing tract hpi or lead_hpi

replace lead_hpi_tract = . if lead_hpi_tract != . & hpi_tract == .

assert hpi_tract != . if lead_hpi_tract != .

assert lead_hpi_tract != . if hpi_tract != .


replace hpi_zip = . if hpi_zip !=. & lead_hpi_zip == .  // deleting obs with missing zip hpi or lead_hpi

replace lead_hpi_zip = . if lead_hpi_zip != . & hpi_zip == .

assert hpi_zip != . if lead_hpi_zip != .

assert lead_hpi_zip != . if hpi_zip != .


gen hpi_tractzip = hpi_tract  // combining tract and zip

replace hpi_tractzip = hpi_zip if hpi_tractzip == . & hpi_zip != .

gen lead_hpi_tractzip = lead_hpi_tract

replace lead_hpi_tractzip = lead_hpi_zip if lead_hpi_tractzip == . & lead_hpi_zip != .


rename (hpi_tract lead_hpi_tract hpi_zip lead_hpi_zip hpi_tractzip lead_hpi_tractzip)(hpi_tract_orig lead_hpi_tract_orig hpi_zip_orig lead_hpi_zip_orig hpi_tractzip_orig lead_hpi_tractzip_orig)

gen hpi_tractzip_orig_adj = hpi_tractzip_orig +(lead_hpi_tractzip_orig - hpi_tractzip_orig)/12*origmonth // exact tract/zip hpi


gen hpi_tractzip_orig_id = "tract" if hpi_tract_orig != .

replace hpi_tractzip_orig_id = "zip" if hpi_tractzip_orig_id == "" & hpi_zip_orig != .

rename year origyear


// 3. ADDING TRACT HPI DATA AT TERMINATION - USE ZIP HPI IF TRACT HPI MISSING

rename repyear year

rename repmonth month

merge m:1 tract year using ".\output_data\HPI_AT_BDL_tract.dta", keepusing(hpi lead_hpi)

drop if _merge == 2

drop _merge

rename (hpi lead_hpi)(hpi_tract lead_hpi_tract)


merge m:1 fivedigitzipcode year using ".\output_data\HPI_AT_BDL_ZIP5.dta", keepusing(hpi led_hpi)

drop if _merge == 2

drop _merge

rename (hpi led_hpi)(hpi_zip lead_hpi_zip)


replace hpi_tract = . if hpi_tract !=. & lead_hpi_tract == . // deleting obs with missing tract hpi or lead_hpi

replace lead_hpi_tract = . if lead_hpi_tract != . & hpi_tract == .

assert hpi_tract != . if lead_hpi_tract != .

assert lead_hpi_tract != . if hpi_tract != .


replace hpi_zip = . if hpi_zip !=. & lead_hpi_zip == .  // deleting obs with missing zip hpi or lead_hpi

replace lead_hpi_zip = . if lead_hpi_zip != . & hpi_zip == .

assert hpi_zip != . if lead_hpi_zip != .

assert lead_hpi_zip != . if hpi_zip != .


gen hpi_tractzip = hpi_tract  // combining tract and zip

replace hpi_tractzip = hpi_zip if hpi_tractzip == . & hpi_zip != .

gen lead_hpi_tractzip = lead_hpi_tract

replace lead_hpi_tractzip = lead_hpi_zip if lead_hpi_tractzip == . & lead_hpi_zip != .


rename (hpi_tract lead_hpi_tract hpi_zip lead_hpi_zip hpi_tractzip lead_hpi_tractzip)(hpi_tract_end lead_hpi_tract_end hpi_zip_end lead_hpi_zip_end hpi_tractzip_end lead_hpi_tractzip_end)

gen hpi_tractzip_end_adj = hpi_tractzip_end +(lead_hpi_tractzip_end - hpi_tractzip_end)/12*month // exact tract/zip hpi

br origyear origmonth year month hpi_tractzip_orig_adj hpi_tractzip_orig_id hpi_tract_end lead_hpi_tract_end hpi_zip_end lead_hpi_zip_end hpi_tractzip_end lead_hpi_tractzip_end hpi_tractzip_end_adj

gen hpi_tractzip_end_id = "tract" if hpi_tract_end!= .

replace hpi_tractzip_end_id = "zip" if hpi_tractzip_end_id == "" & hpi_zip_end != .


br origyear origmonth year month hpi_tractzip_orig_adj hpi_tractzip_orig_id hpi_tract_end lead_hpi_tract_end hpi_zip_end lead_hpi_zip_end hpi_tractzip_end lead_hpi_tractzip_end hpi_tractzip_end_adj hpi_tractzip_end_id if hpi_tractzip_orig_id != hpi_tractzip_end_id

count if hpi_tractzip_orig_id != hpi_tractzip_end_id


// Deleting obs with not matching tract or zip HPIs

gen var = 1 if hpi_tractzip_orig_id != hpi_tractzip_end_id

replace hpi_tractzip_orig_id = "" if var == 1

replace hpi_tractzip_end_id = "" if var == 1

drop var

assert hpi_tractzip_orig_id == hpi_tractzip_end_id


// 4. COMPUTING ADJUSTED PROPERTY VALUES AND EQUITY BASED ON TRACT/ZIP HPIs UNDER CASE 2: FIRST AND MATCHED SECOND LIENS 

gen adj_propvalue_tractzip = originalappraisedvalue*hpi_tractzip_end_adj/hpi_tractzip_orig_adj if hpi_tractzip_orig_id == hpi_tractzip_end_id

br origyear origmonth year month originalappraisedvalue adj_propvalue hpi_tractzip_orig_adj hpi_tractzip_orig_id hpi_tractzip_end_adj hpi_tractzip_end_id adj_propvalue_tractzip

gen equity_1st2nd_tractzip = adj_propvalue_tractzip - endloanbal_1st2nd_match

sum equity_1st2nd_tractzip

sum equity_1st2nd_tractzip if hpi_tractzip_orig_id == "tract"

gen equity_1st2nd_ratio_tractzip = equity_1st2nd_tractzip / adj_propvalue_tractzip

label var equity_1st2nd_ratio_tractzip "Equity Ratio 1st2nd Match hpitractzi

sum equity_1st2nd_ratio_tractzip, d

gen negequity_1st2nd_tractzip_dum = (equity_1st2nd_ratio_tractzip < 0)

label var negequity_1st2nd_tractzip_dum "Negative Equity 1st2nd hpitractzip dum"


gen log_adjvalue_tractzip=ln(adj_propvalue_tractzip)

label var log_adjvalue_tractzip "Terminal Property Value TractZip"

save ".\output_data\regressions_dataset.dta", replace


// III. CHECKING STUDY SAMPLE AND VARIOUS SUBSAMPLES

use ".\output_data\regressions_dataset.dta", clear

count if sr_property_id != .

tab paidoff if sr_property_id != .

tab foreclosed if sr_property_id != .

tab active if sr_property_id != .

// First end-date matches

count if  enddate_diff != .

sum enddate_diff

count if enddate_diff <= 2 

tab purchase_2 if enddate_diff <= 2 

tab purchase_2 negequity_1st2nd_tractzip_dum if enddate_diff <= 2 

tab negequity_1st2nd_tractzip_dum if enddate_diff <= 2 

count if enddate_diff > 2 & enddate_diff != .

gen endmatch1 =  enddate_diff <= 2

tab endmatch1, m 

// Second end-date matched

count if enddate_diff_asshist != .

sum enddate_diff_asshist

count if enddate_diff_asshist >= -2 & enddate_diff_asshist <= 2

count if enddate_diff > 2 & enddate_diff_asshist >= -2 & enddate_diff_asshist <= 2

count if enddate_diff_asshist != . & enddate_diff_asshist >= -2 

count if enddate_diff > 2 & enddate_diff_asshist != . & enddate_diff_asshist >= -2 

gen endmatch2 = enddate_diff_asshist != . & enddate_diff_asshist >= -2 

tab endmatch2

tab endmatch1 endmatch2

count if enddate_diff <=2 & endmatch2 == 1

count if sr_property_id != . & paidoff == 1 & (purchase_2 == . | endmatch1 == 0)

count if sr_property_id != . & paidoff == 1 & endmatch1 == 0


gen sample1 = 1

replace sample1 = 0 if sr_property_id != . & paidoff == 1 & endmatch1 == 0

tab sample1 endmatch1, m 

tab sample1 endmatch2, m 

gen sample2 = sample1

replace sample2 = 1 if endmatch2 == 1

tab sample2 endmatch1, m 

tab sample2 endmatch2, m 

tab sample1 sample2, m 


// Loans with no missing property ids

gen studysample = 0 if sr_property_id != .

count if studysample != .

// Foreclose loans

replace studysample = 1 if studysample == 0 & foreclosed == 1 & sr_property_id != .

// Repayments from 1st match of paidoff terminations using liens data

count if sample2 == 1 & endmatch1 == 1

count if studysample == 0 & sample2 == 1 & endmatch1 == 1 & paidoff == 1

count if studysample == 0 & sample2 == 1 & endmatch1 == 1 & purchase_2 == 1

drop prepayment

gen prepayment = 0 if studysample != .

replace prepayment = 1 if prepayment == 0 & studysample == 0 & sample2 == 1 & endmatch1 == 1 & purchase_2 == 1

replace studysample = 1 if studysample == 0 &  prepayment == 1

// Refinancing from 1st match of paidoff terminations using liens data

count if studysample == 0 & sample2 == 1 & endmatch1 == 1 & purchase_2 == 0

drop refinancing

gen refinancing = 0 if studysample != .

replace refinancing = 1 if refinancing == 0 & studysample == 0 & sample2 == 1 & endmatch1 == 1 & purchase_2 == 0

replace studysample = 1 if studysample == 0 & refinancing == 1

// Repayments from 2nd match of paidoff terminations using assessor data

count if studysample == 0 & enddate_diff_asshist != . & enddate_diff_asshist >= -2 

count if studysample == 0 & prepayment == 0 & enddate_diff_asshist != . & enddate_diff_asshist >= -2 

replace prepayment = 1 if studysample == 0 & prepayment == 0 & enddate_diff_asshist != . & enddate_diff_asshist >= -2 

replace studysample = 1 if studysample == 0 & prepayment == 1 & enddate_diff_asshist != . & enddate_diff_asshist >= -2 

// Checks

tab studysample, m

tab prepayment studysample, m

tab refinancing studysample, m 

tab foreclosed studysample, m

count if prepayment == 1 | foreclosed == 1 & studysample == 1

count if (prepayment == 1 | foreclosed == 1) & studysample == 1

count if (prepayment == 1 | foreclosed == 1) & studysample == 1 & hpi_tractzip_orig_id == ""

count if (prepayment == 1 | foreclosed == 1) & studysample == 1 & shortsaleflag == "True"

count if (prepayment == 1 | foreclosed == 1) & studysample == 1 & shortsaleflag == "False" & hpi_tractzip_orig_id != ""


// IV. GENERATING STUDY TABLES

gen negquity_interact_tractzip = negequity_1st2nd_tractzip_dum * equity_1st2nd_ratio_tractzip 

label var negquity_interact_tractzip "Negative Equity Dummy x Equity Ratio"

gen negeq_crscore_interact_tractzip = negequity_1st2nd_tractzip_dum * creditscore_sh

label var negeq_crscore_interact_tractzip "Negative Equity Dummy x Credit Score"

gen creditscore_recourse = creditscore_sh*recourse
label var creditscore_recourse "Credit Score x Recourse"

gen equity_recouse = equity_1st2nd_ratio_tractzip*recourse
label var equity_recouse "Equity x Recourse"

gen propval_recourse = log_adjvalue_tractzip*recourse
label var propval_recourse "Property Value x Recourse"

sum equity_1st2nd_tractzip

gen equity_10thds = equity_1st2nd_tractzip/10000

label var equity_10thds "Equity Amount ($0000s)"

gen equity_10thds_recouse = equity_10thds*recourse
label var equity_10thds_recouse "Equity Amount ($0000s) x Recourse"

gen propvalue_10thds = adj_propvalue_tractzip/10000

label var propval_recourse "Property Value"

gen loanamount_10thds = originalloanbalance/10000

label var loanamount_10thds "Loan Amount ($0000s)"



// GENERATING THE STUDY TABLES


// TABLE 1: VARIABLE DESCRIPTION (in the paper)


// TABLE 2: DESCRIPTIVE TABLE (put together manually using the output below)

count if studysample==1 & (prepayment==1 | foreclosed==1) & shortsaleflag=="False" & hpi_tractzip_orig_id != ""

count if sr_property_id!=.

count if studysample==1 & (prepayment==1 | foreclosed==1)

count if studysample==1 & (prepayment==1 | foreclosed==1) & shortsaleflag=="False"

count if studysample==1 & (prepayment==1 | foreclosed==1) & shortsaleflag=="False" & hpi_tractzip_orig_id != ""

sum prepayment foreclosed creditscore_sh equity_10thds negequity_1st2nd_tractzip_dum recourse originalcltv originalltv propvalue_10thds loanamount_10thds loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam cpi mortgage30yr unemployment hpi_end hpi_orig hpi_sd medianinc if studysample==1 & (prepayment==1 | foreclosed==1) & shortsaleflag=="False" & hpi_tractzip_orig_id != ""

sum prepayment foreclosed creditscore_sh equity_10thds negequity_1st2nd_tractzip_dum recourse originalcltv originalltv propvalue_10thds loanamount_10thds loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam cpi mortgage30yr unemployment hpi_end hpi_orig hpi_sd medianinc if studysample==1 & (prepayment==1 | foreclosed==1) & negequity_1st2nd_tractzip_dum==1 & shortsaleflag=="False" & hpi_tractzip_orig_id != ""

sum prepayment foreclosed creditscore_sh equity_10thds negequity_1st2nd_tractzip_dum recourse propvalue_10thds loanamount_10thds originalcltv originalltv loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam cpi mortgage30yr unemployment hpi_end hpi_orig hpi_sd medianinc if studysample==1 & (prepayment==1 | foreclosed==1) & negequity_1st2nd_tractzip_dum==0 & shortsaleflag=="False" & hpi_tractzip_orig_id != ""


// TABLE 3: LOAN TERMINATION BY EQUITY POSITION (put together using the output below)

// Full Sample

count if studysample==1 & prepayment==1 & shortsaleflag=="False" & hpi_tractzip_orig_id != ""

count if studysample==1 & refinancing==1 & shortsaleflag=="False" & hpi_tractzip_orig_id != ""

count if studysample==1 & foreclosed==1 & shortsaleflag=="False" & hpi_tractzip_orig_id != ""

// Negative Equity

count if studysample==1 & prepayment==1 & shortsaleflag=="False" & negequity_1st2nd_tractzip_dum==1 & hpi_tractzip_orig_id != ""

count if studysample==1 & foreclosed==1 & shortsaleflag=="False" & negequity_1st2nd_tractzip_dum==1 & hpi_tractzip_orig_id != ""

// Positive Equity

count if studysample==1 & prepayment==1 & shortsaleflag=="False" & negequity_1st2nd_tractzip_dum==0 & hpi_tractzip_orig_id != ""

count if studysample==1 & foreclosed==1 & shortsaleflag=="False" & negequity_1st2nd_tractzip_dum==0 & hpi_tractzip_orig_id != "" 


// TABLE 4: LOAN TERMINATION BY CREDIT SCORE AND EQUITY QUINTILES (put together manually using the output below)

// Panel A. Credit-Scrore Quintiles

// Positive Equity

bys crdscore_quint_matched: tab loan_outcome1 if studysample==1 & !inlist(loan_outcome1,"Active","Refinanced") & refinancing == 0 & negequity_1st2nd_tractzip_dum==0 & shortsaleflag=="False" & hpi_tractzip_orig_id != ""

bys crdscore_quint_matched: sum equity_1st2nd_ratio_tractzip if studysample==1 & !inlist(loan_outcome1,"Active","Refinanced") & refinancing == 0 & negequity_1st2nd_tractzip_dum==0  & shortsaleflag=="False" & hpi_tractzip_orig_id != ""

bys crdscore_quint_matched: count if studysample==1 & prepayment==1 & negequity_1st2nd_tractzip_dum==0 & shortsaleflag=="False"  & hpi_tractzip_orig_id != ""

bys crdscore_quint_matched: count if studysample==1 & foreclosed==1 & negequity_1st2nd_tractzip_dum==0 & shortsaleflag=="False" & hpi_tractzip_orig_id != ""

// Negative Equity

bys crdscore_quint_matched: tab loan_outcome1 if studysample==1 & !inlist(loan_outcome1,"Active","Refinanced") & refinancing == 0 & negequity_1st2nd_tractzip_dum==1  & shortsaleflag=="False" & hpi_tractzip_orig_id != ""

bys crdscore_quint_matched: sum equity_1st2nd_ratio_tractzip if studysample==1 & !inlist(loan_outcome1,"Active","Refinanced") & refinancing == 0 & negequity_1st2nd_tractzip_dum==1 & shortsaleflag=="False" & hpi_tractzip_orig_id != ""

// Panel B. Equity Quintiles

// Positive Equity

xtile posequity_quint_1st2nd_tractzip = equity_1st2nd_ratio_tractzip if sr_property_id!=. & negequity_1st2nd_tractzip_dum==0 & hpi_tractzip_orig_id != "", nq(5)

tab posequity_quint_1st2nd_tractzip

gen posequity_quint1_1st2nd_tractzip= posequity_quint_1st2nd_tractzip==1
tab posequity_quint1_1st2nd_tractzip

gen posequity_quint2_1st2nd_tractzip= posequity_quint_1st2nd_tractzip==2
gen posequity_quint3_1st2nd_tractzip= posequity_quint_1st2nd_tractzip==3
gen posequity_quint4_1st2nd_tractzip= posequity_quint_1st2nd_tractzip==4
gen posequity_quint5_1st2nd_tractzip= posequity_quint_1st2nd_tractzip==5

*assert posequity_quint1_1st2nd_tractzip + posequity_quint2_1st2nd_tractzip + posequity_quint3_1st2nd_tractzip + posequity_quint4_1st2nd_tractzip + posequity_quint5_1st2nd_tractzip if sr_property_id!=. & negequity_1st2nd_tractzip_dum==0 & hpi_tractzip_orig_id != "" // Include this test when running on real data

label var posequity_quint1_1st2nd_tractzip "Positive Equity Quintile 1 1st2nd TractZip"
label var posequity_quint2_1st2nd_tractzip "Positive Equity Quintile 2 1st2nd TractZip"
label var posequity_quint3_1st2nd_tractzip "Positive Equity Quintile 3 1st2nd TractZip"
label var posequity_quint4_1st2nd_tractzip "Positive Equity Quintile 4 1st2nd TractZip"
label var posequity_quint5_1st2nd_tractzip "Positive Equity Quintile 5 1st2nd TractZip"

bys posequity_quint_1st2nd_tractzip: tab loan_outcome1 if studysample==1 & !inlist(loan_outcome1,"Active","Refinanced") & refinancing == 0  & shortsaleflag=="False"

bys posequity_quint_1st2nd_tractzip: sum equity_1st2nd_ratio_tractzip if studysample==1 & !inlist(loan_outcome1,"Active","Refinanced") & refinancing == 0  & shortsaleflag=="False"

// Negative Equity

xtile negequity_quint_1st2nd_tractzip = equity_1st2nd_ratio_tractzip if sr_property_id!=. & negequity_1st2nd_tractzip_dum==1 & hpi_tractzip_orig_id != "", nq(5)

tab negequity_quint_1st2nd_tractzip

gen negequity_quint1_1st2nd_tractzip= negequity_quint_1st2nd_tractzip==1
tab negequity_quint1_1st2nd_tractzip

gen negequity_quint2_1st2nd_tractzip= negequity_quint_1st2nd_tractzip==2
gen negequity_quint3_1st2nd_tractzip= negequity_quint_1st2nd_tractzip==3
gen negequity_quint4_1st2nd_tractzip= negequity_quint_1st2nd_tractzip==4
gen negequity_quint5_1st2nd_tractzip= negequity_quint_1st2nd_tractzip==5

*assert negequity_quint1_1st2nd_tractzip + negequity_quint2_1st2nd_tractzip + negequity_quint3_1st2nd_tractzip + negequity_quint4_1st2nd_tractzip + negequity_1st2nd_tractzip_dum if sr_property_id!=. & negequity_1st2nd_tractzip_dum==1 // Include this test when running on real data

label var negequity_quint1_1st2nd_tractzip "Negative Equity Quintile 1 1st2nd TractZip"
label var negequity_quint2_1st2nd_tractzip "Negative Equity Quintile 2 1st2nd TractZip"
label var negequity_quint3_1st2nd_tractzip "Negative Equity Quintile 3 1st2nd TractZip"
label var negequity_quint4_1st2nd_tractzip "Negative Equity Quintile 4 1st2nd TractZip"
label var negequity_quint5_1st2nd_tractzip "Negative Equity Quintile 5 1st2nd TractZip"

bys negequity_quint_1st2nd_tractzip: tab loan_outcome1 if studysample==1 & !inlist(loan_outcome1,"Active","Refinanced") & refinancing == 0  & shortsaleflag=="False"

bys negequity_quint_1st2nd_tractzip: sum equity_1st2nd_ratio_tractzip if studysample==1 & !inlist(loan_outcome1,"Active","Refinanced") & refinancing == 0  & shortsaleflag=="False"


// TABLES 5 & B4: LOAN REPAYMENT vs. FORECLOSURE POOLED WITH RECOURSE INTERACTIONS

eststo clear

reg prepayment equity_10thds creditscore_sh propvalue_10thds loanamount_10thds loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam cpi mortgage30yr unemployment hpi_end hpi_orig hpi_sd medianinc if studysample==1 & (prepayment==1 | foreclosed==1) & shortsaleflag=="False" & hpi_tractzip_orig_id != "" & negequity_1st2nd_tractzip_dum==0 // When using real data: replace reg with reghdfe and use add these options "absorb(origyear year fivedigitzipcode) cluster(fivedigitzipcode)" 

*eststo reg1 // Activate to store result 

reg prepayment equity_10thds creditscore_sh propvalue_10thds loanamount_10thds loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam cpi mortgage30yr unemployment hpi_end hpi_orig hpi_sd medianinc if studysample==1 & (prepayment==1 | foreclosed==1) & shortsaleflag=="False" & hpi_tractzip_orig_id != "" & negequity_1st2nd_tractzip_dum==1 // When using real data: replace reg with reghdfe and use add these options, absorb(origyear year fivedigitzipcode) cluster(fivedigitzipcode)

*eststo reg2  // Activate to store result 

reg prepayment equity_10thds negequity_1st2nd_tractzip_dum#c.equity_10thds creditscore_sh negequity_1st2nd_match_dum#c.creditscore_sh propvalue_10thds loanamount_10thds loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam cpi mortgage30yr unemployment hpi_end hpi_orig hpi_sd medianinc if studysample==1 & (prepayment==1 | foreclosed==1) & shortsaleflag=="False" & hpi_tractzip_orig_id != "" // When using real data: replace reg with reghdfe and use add these options, absorb(origyear year fivedigitzipcode) cluster(fivedigitzipcode)

*eststo reg3  // Activate to store result 

reg prepayment equity_10thds equity_10thds_recouse creditscore_sh creditscore_recourse propvalue_10thds loanamount_10thds loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam cpi mortgage30yr unemployment hpi_end hpi_orig hpi_sd medianinc if studysample==1 & (prepayment==1 | foreclosed==1) & shortsaleflag=="False" & hpi_tractzip_orig_id != "" & negequity_1st2nd_tractzip_dum==1  // When using real data: replace reg with reghdfe and use add these options, absorb(origyear year fivedigitzipcode) cluster(fivedigitzipcode)

*eststo reg4  // Activate to store result 

*esttab reg1  reg2 reg3 reg4 using "D:\Dropbox\Underwater Mortgages\results\table5_tractzip_recourse_may5.csv", label b(4) se(4) depvars ar2 nonotes nogaps star(* 0.05 ** 0.01 *** 0.001) mtitles("Repaid PosEq" "Repaid NegEq" "Repaid Full" "Repaid NegEq") title(Repaid vs foreclosed: Matched Sample) replace // Activate to save table


// TABLE 6: DEFAULT COSTS REVISED

// Repaid (Repayers) Negative Equity Loans by Credit Score Quintiles

bys crdscore_quint_matched: sum equity_1st2nd_tractzip if studysample==1 & prepayment==1 & negequity_1st2nd_tractzip_dum==1 & shortsaleflag=="False" & hpi_tractzip_orig_id != "", d 

bys crdscore_quint_matched: sum adj_propvalue_tractzip if studysample==1 & prepayment==1 & negequity_1st2nd_tractzip_dum==1 & shortsaleflag=="False" & hpi_tractzip_orig_id != "", d 

gen default_cost = -equity_1st2nd_tractzip + 0.06*adj_propvalue_tractzip

label var default_cost "Default Cost"

bys crdscore_quint_matched: sum default_cost if studysample==1 & prepayment==1 & negequity_1st2nd_tractzip_dum==1 & shortsaleflag=="False" & hpi_tractzip_orig_id != "", d 


// Foreclosed (Defaulters) Negative Equity Loans by Credit Score Quintiles 

bys crdscore_quint_matched: sum equity_1st2nd_tractzip if studysample==1 & foreclosed==1 & negequity_1st2nd_tractzip_dum==1 & shortsaleflag=="False" & hpi_tractzip_orig_id != "", d

bys crdscore_quint_matched: sum adj_propvalue_tractzip if studysample==1 &  foreclosed==1 & negequity_1st2nd_tractzip_dum==1 & shortsaleflag=="False" & hpi_tractzip_orig_id != "", d

bys crdscore_quint_matched: sum default_cost if studysample==1 &  foreclosed==1 & negequity_1st2nd_tractzip_dum==1 & shortsaleflag=="False" & hpi_tractzip_orig_id != "", d


// FIGURE 2: HISTOGRAMS OF DEFAULT COSTS

gen _default_cost = default_cost/1000

label var _default_cost "Default Cost"

histogram _default_cost if studysample==1 & negequity_1st2nd_tractzip_dum==1 & shortsaleflag=="False" & hpi_tractzip_orig_id != "" & default_cost<=400000 & prepayment==1 , bin(40) percent xtitle(Lower Bound Default Costs (000s)) by(, subtitle(Lower Bound Default Costs by Credit-Score Quintiles)) by(crdscore_quint_matched)

graph export ".\output_data\default_costs_repayers.pdf", as(pdf) name("Graph") replace


histogram _default_cost if studysample==1 & negequity_1st2nd_tractzip_dum==1 & shortsaleflag=="False" & hpi_tractzip_orig_id != "" & default_cost<=400000 & prepayment==0, bin(20) percent xtitle(Default Costs (000s)) by(, title(Defaulters) subtitle(Upper Bound Default Costs by Credit-Score Quintiles)) by(crdscore_quint_matched)

graph export ".\output_data\default_costs_defaulters.pdf", as(pdf) name("Graph")


// TABLE B5: LOAN REPAYMENT vs. FORECLOSURE: BY CREDIT SCORE QUINTILES

eststo clear

reg prepayment i.crdscore_quint_matched equity_10thds propvalue_10thds loanamount_10thds loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam cpi mortgage30yr unemployment hpi_end hpi_orig hpi_sd medianinc if negequity_1st2nd_tractzip_dum==0 & studysample==1 & (prepayment==1 | foreclosed==1)  & shortsaleflag=="False" & hpi_tractzip_orig_id != ""  // When using real data: replace reg with reghdfe and use add these options, absorb(origyear year fivedigitzipcode) cluster(fivedigitzipcode)

*eststo reg1 // Activate to store result

reg prepayment i.crdscore_quint_matched##c.equity_10thds propvalue_10thds loanamount_10thds loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam cpi mortgage30yr unemployment hpi_end hpi_orig hpi_sd medianinc if negequity_1st2nd_tractzip_dum==0 & studysample==1 & (prepayment==1 | foreclosed==1)  & shortsaleflag=="False" & hpi_tractzip_orig_id != "" // When using real data: replace reg with reghdfe and use add these options, absorb(origyear year fivedigitzipcode) cluster(fivedigitzipcode)

*eststo reg2 // Activate to store result

reg prepayment i.crdscore_quint_matched equity_10thds propvalue_10thds loanamount_10thds loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam cpi mortgage30yr unemployment hpi_end hpi_orig hpi_sd medianinc if negequity_1st2nd_tractzip_dum==1 & studysample==1 & (prepayment==1 | foreclosed==1) & shortsaleflag=="False" & hpi_tractzip_orig_id != "" // When using real data: replace reg with reghdfe and use add these options, absorb(origyear year fivedigitzipcode) cluster(fivedigitzipcode)

*eststo reg3 // Activate to store result

reg prepayment i.crdscore_quint_matched##c.equity_10thds propvalue_10thds loanamount_10thds loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam cpi mortgage30yr unemployment hpi_end hpi_orig hpi_sd medianinc if negequity_1st2nd_tractzip_dum==1 & studysample==1 & (prepayment==1 | foreclosed==1) & shortsaleflag=="False" & hpi_tractzip_orig_id != "" // When using real data: replace reg with reghdfe and use add these options, absorb(origyear year fivedigitzipcode) cluster(fivedigitzipcode)

*eststo reg4 // Activate to store result

*esttab reg1 reg2 reg3 reg4 using ".\output_data\table_creditscore_quint.csv", label b(4) se(4) depvars ar2 nonotes nogaps star(* 0.05 ** 0.01 *** 0.001) mtitles("Repaid PoEq" "Repaid PoEq" "Repaid NegEq" "Repaid NegEq") title(Repaid vs foreclosed by Credit Score Quintile for Negative and Positive Equity Loans (Matched Sample)) replace  // Activate to save table


// TABLE B6: LOAN REPAYMENT vs. FORECLOSURE: BY EQUITY QUINTILES

eststo clear

reg prepayment creditscore_sh i.posequity_quint_1st2nd_tractzip propvalue_10thds loanamount_10thds loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam cpi mortgage30yr unemployment hpi_end hpi_orig hpi_sd medianinc if negequity_1st2nd_tractzip_dum==0 & studysample==1 & (prepayment==1 | foreclosed==1)  & shortsaleflag=="False" & hpi_tractzip_orig_id != "" // When using real data: replace reg with reghdfe and use add these options, absorb(origyear year fivedigitzipcode) cluster(fivedigitzipcode)

*eststo reg1 // Activate to store result

reg prepayment i.posequity_quint_1st2nd_tractzip##c.creditscore_sh propvalue_10thds loanamount_10thds loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam cpi mortgage30yr unemployment hpi_end hpi_orig hpi_sd medianinc if negequity_1st2nd_tractzip_dum==0 & studysample==1 & (prepayment==1 | foreclosed==1)  & shortsaleflag=="False" & hpi_tractzip_orig_id != "" // When using real data: replace reg with reghdfe and use add these options, absorb(origyear year fivedigitzipcode) cluster(fivedigitzipcode)

*eststo reg2 // Activate to store result

reg prepayment creditscore_sh i.negequity_quint_1st2nd_tractzip propvalue_10thds loanamount_10thds loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam cpi mortgage30yr unemployment hpi_end hpi_orig hpi_sd medianinc if negequity_1st2nd_tractzip_dum==1 & studysample==1 & (prepayment==1 | foreclosed==1)  & shortsaleflag=="False" & hpi_tractzip_orig_id != "" // When using real data: replace reg with reghdfe and use add these options, absorb(origyear year fivedigitzipcode) cluster(fivedigitzipcode)

*eststo reg3 // Activate to store result

reg prepayment i.negequity_quint_1st2nd_tractzip##c.creditscore_sh propvalue_10thds loanamount_10thds loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam cpi mortgage30yr unemployment hpi_end hpi_orig hpi_sd medianinc if negequity_1st2nd_tractzip_dum==1 & studysample==1 & (prepayment==1 | foreclosed==1)  & shortsaleflag=="False" & hpi_tractzip_orig_id != "" // When using real data: replace reg with reghdfe and use add these options, absorb(origyear year fivedigitzipcode) cluster(fivedigitzipcode)

*eststo reg4 // Activate to store result

*esttab reg1 reg2 reg3 reg4 using ".\output_data\table_equity_quint.csv", label b(4) se(4) depvars ar2 nonotes nogaps star(* 0.05 ** 0.01 *** 0.001) mtitles("Repaid PoEq" "Repaid PoEq" "Repaid NegEq" "Repaid NegEq") title(Repaid vs foreclosed by Equity Quintile for Negative and Positive Equity Loans (Matched Sample)) replace // Activate to save table


// TABLE B7:  ROBUSTNESS CHECKS Controlling for Measurement Error, Default, and Borrower Income 

eststo clear

reg prepayment equity_10thds creditscore_sh propvalue_10thds loanamount_10thds loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam cpi mortgage30yr unemployment hpi_end hpi_orig hpi_sd medianinc if negequity_1st2nd_tractzip_dum==0 & studysample==1 & (prepayment==1 | foreclosed==1) & equity_1st2nd_ratio_tractzip>=.05 & shortsaleflag=="False" & hpi_tractzip_orig_id != "" // When using real data: replace reg with reghdfe and use add these options, absorb(origyear year fivedigitzipcode) cluster(fivedigitzipcode)

*eststo reg1a // Activate to store result

reg prepayment equity_10thds creditscore_sh propvalue_10thds loanamount_10thds loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam cpi mortgage30yr unemployment hpi_end hpi_orig hpi_sd medianinc if negequity_1st2nd_tractzip_dum==0 & studysample==1 & (prepayment==1 | foreclosed==1) & default==0 & shortsaleflag=="False" & hpi_tractzip_orig_id != "" // When using real data: replace reg with reghdfe and use add these options, absorb(origyear year fivedigitzipcode) cluster(fivedigitzipcode)

*eststo reg2a // Activate to store result

reg prepayment equity_10thds creditscore_sh propvalue_10thds loanamount_10thds loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam cpi mortgage30yr unemployment hpi_end hpi_orig hpi_sd medianinc if negequity_1st2nd_tractzip_dum==0 & studysample==1 & (prepayment==1 | foreclosed==1) & default==0 & equity_1st2nd_ratio_tractzip>=.05 & shortsaleflag=="False" & hpi_tractzip_orig_id != "" // When using real data: replace reg with reghdfe and use add these options, absorb(origyear year fivedigitzipcode) cluster(fivedigitzipcode)

*eststo reg3a // Activate to store result

reg prepayment equity_10thds creditscore_sh propvalue_10thds loanamount_10thds loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam cpi mortgage30yr unemployment hpi_end hpi_orig hpi_sd medianinc income_estim if negequity_1st2nd_tractzip_dum==0 & studysample==1 & (prepayment==1 | foreclosed==1) & default==0 & equity_1st2nd_ratio_tractzip>=.05 & shortsaleflag=="False" & hpi_tractzip_orig_id != "" // When using real data: replace reg with reghdfe and use add these options, absorb(origyear year fivedigitzipcode) cluster(fivedigitzipcode)

*eststo reg4a // Activate to store result

reg prepayment equity_10thds creditscore_sh propvalue_10thds loanamount_10thds loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam cpi mortgage30yr unemployment hpi_end hpi_orig hpi_sd medianinc if negequity_1st2nd_tractzip_dum==1 & studysample==1 & (prepayment==1 | foreclosed==1) & equity_1st2nd_ratio_tractzip<=-.05 & shortsaleflag=="False" & hpi_tractzip_orig_id != "" // When using real data: replace reg with reghdfe and use add these options, absorb(origyear year fivedigitzipcode) cluster(fivedigitzipcode)

*eststo reg1b // Activate to store result

reg prepayment equity_10thds creditscore_sh propvalue_10thds loanamount_10thds loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam cpi mortgage30yr unemployment hpi_end hpi_orig hpi_sd medianinc if negequity_1st2nd_tractzip_dum==1 & studysample==1 & (prepayment==1 | foreclosed==1) & default==0 & shortsaleflag=="False" & hpi_tractzip_orig_id != "" // When using real data: replace reg with reghdfe and use add these options, absorb(origyear year fivedigitzipcode) cluster(fivedigitzipcode)

*eststo reg2b // Activate to store result

reg prepayment equity_10thds creditscore_sh propvalue_10thds loanamount_10thds loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam cpi mortgage30yr unemployment hpi_end hpi_orig hpi_sd medianinc if negequity_1st2nd_tractzip_dum==1 & studysample==1 & (prepayment==1 | foreclosed==1) & default==0 & equity_1st2nd_ratio_tractzip<=-.05 & shortsaleflag=="False" & hpi_tractzip_orig_id != "" // When using real data: replace reg with reghdfe and use add these options, absorb(origyear year fivedigitzipcode) cluster(fivedigitzipcode)

*eststo reg3b // Activate to store result

reg prepayment equity_10thds creditscore_sh propvalue_10thds loanamount_10thds loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam cpi mortgage30yr unemployment hpi_end hpi_orig hpi_sd medianinc income_estim if negequity_1st2nd_tractzip_dum==1 & studysample==1 & (prepayment==1 | foreclosed==1) & default==0 & equity_1st2nd_ratio_tractzip<=-.05 & shortsaleflag=="False" & hpi_tractzip_orig_id != "" // When using real data: replace reg with reghdfe and use add these options, absorb(origyear year fivedigitzipcode) cluster(fivedigitzipcode)

*eststo reg4b // Activate to store result

*esttab reg1a reg2a reg3a reg4a reg1b reg2b reg3b reg4b using ".\output_data\table_robutness_checks.csv", label b(4) se(4) depvars ar2 nonotes nogaps star(* 0.05 ** 0.01 *** 0.001) mtitles("meas_err PoEq" "def PoEq" "meas_err def PoEq" "meas_err def inc PoEq" "meas_err NegEq" "def NegEq" "meas_err def NegEq" "meas_err def inc NegEq") title(Robustness Checks: Repaid vs foreclosed Using Main Specification) replace // Activate to save table


// ADDITIONAL APPENDIX TABLES 

// TABLE B1: Matched vs. Unmatched Loans At Origination

sum creditscore_sh equity_10thds propvalue_10thds loanamount_10thds negequity_1st2nd_tractzip_dum originalcltv originalltv loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam if sr_property_id!=. 

sum creditscore_sh equity_10thds propvalue_10thds loanamount_10thds negequity_1st2nd_tractzip_dum originalcltv originalltv loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam if sr_property_id ==. 


// TABLE B2: Matched vs. Unmatched Paidoff Loans at Terminations

tab endmatch1 if paidoff == 1 & sr_property_id != .

sum creditscore_sh equity_10thds propvalue_10thds loanamount_10thds negequity_1st2nd_tractzip_dum originalcltv originalltv loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam if paidoff == 1 & sr_property_id != . & endmatch1 == 1

sum creditscore_sh equity_10thds propvalue_10thds loanamount_10thds negequity_1st2nd_tractzip_dum originalcltv originalltv loanpurp_refi nonowneroccup occupunknown interest_win log_term dti dti_miss pmi pmi_miss negamort arm balloon io io_miss singlefam if paidoff == 1 & sr_property_id != . & endmatch1 == 0


// TABLE B3: ABSNET SAMPLE VS NMB DATA (this is only for the ABSNet portion of the table)

gen _nonowneroccup = nonowneroccup
replace _nonowneroccup = . if occupunknown == 1

gen _pmi = pmi
replace _pmi = . if pmi_miss == 1

gen _io = io
replace _io = . if io_miss == 1

sum creditscore_sh originalcltv originalltv loanpurp_refi _nonowneroccup log_term debttoincomeratio _pmi arm balloon _io singlefam if studysample==1 & (prepayment==1 | foreclosed==1) & shortsaleflag=="False" & hpi_tractzip_orig_id != "" 

// THE END